/*
 * MembershipDAO.java
 *
 * Created on February 12, 2007, 9:10 PM
 */

package edu.byu.isys413.group1E.data;
import edu.byu.isys413.group1E.handlers.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;


/** Reads and writes MembershipBO info to and from the DB.
 *
 * @author Travis Kulbeth
 */
public class MembershipDAO {
    
    /** Contains an instance of MembershipDAO*/
    private static MembershipDAO instance = null;
    
    /** Creates a new instance of MembershipDAO */
    public MembershipDAO() {
    }
    
    
    /**Retrieves an instance of the MembershipDAO object
     *
     * @return a current singleton instance of the MembershipDAO class
     */
    public static synchronized MembershipDAO getInstance() {
        if (instance == null) {
            instance = new MembershipDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * Creates a new MembershipBO object with a newly generated GUID.
     *
     * @return a newly created MembershipBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public MembershipBO create() throws Exception{
        String id = GUID.generate();
        MembershipBO bo = new MembershipBO(id);
        bo.setMembPwd("password");  //hardcoded the membership password for the first iteration
        Cache c = Cache.getInstance();
        c.put(bo.getMembershipID(), bo);
        
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing Membership record
     * from the database based on the Membership id and returns a MembershipBO.
     * 
     * 
     * @return a MembershipBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized MembershipBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
/*******************I commented this out due to the need to make sure current information is 
 *drawn from the central database and not the local cache.
 * 
//        Cache c = Cache.getInstance();
//        MembershipBO member = (MembershipBO)c.get(id);
//        if (member != null){
//            return member;                                                        // if so, return it immediately
//        }
 **********************************************************************************************/
        MembershipBO member = null;
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            member = this.read(id, conn);                                              // call read with a connection
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id=" + id, e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return member;
    }
    
    
    /**
     * This is the public read statement.  It loads an existing Membership record
     * from the database based on the Membership id, using an already existing connection to the DB,
     * and returns a MembershipBO.
     *
     * @return MembershipBO
     * @param id contains the given id for the Membership to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    synchronized MembershipBO read(String id, Connection conn) throws SQLException, DataException {
        // check to see if id in the cache

        Cache c = Cache.getInstance();

/*******************I commented this out due to the need to make sure current information is 
 *drawn from the central database and not the local cache.
 * 
//        MembershipBO member = (MembershipBO)c.get(id);
//        if (member != null){
//            return member;                                                        // if so, return it immediately
//        }
 **********************************************************************************************/
        MembershipBO member = null;
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Membership WHERE membershipID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            member = new MembershipBO(rs.getString("membershipid"));
            member.setCreditCardType(rs.getString("creditcardtype"));
            member.setCreditCardNum(rs.getString("creditcardnum"));
            member.setCreditCardExpMonth(rs.getInt("creditcardexpmonth"));
            member.setCreditCardExpYear(rs.getInt("creditcardexpyear"));
            member.setMemberStartDate(rs.getDate("memberstartdate").toString());
            member.setMemberExpireDate(rs.getDate("memberexpiredate").toString());
            if(rs.getString("BackupExpDate") != null){
                member.setBuExpDate(rs.getDate("BackupExpDate").toString());
                member.setBuSize(rs.getDouble("BackupSize"));
            }else{
                String is = null;
                member.setBuExpDate(null);
                member.setBuSize(0.0);
            }
            member.setMembPwd(rs.getString("membPwd"));
            member.setAlreadyInDB(true);
            member.setIsDirty(false);
            
            // save MembershipBO to the cache
            c.put(member.getMembershipID(), member);
            
            //construct sub-objects: Area of Interest List
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Member_Interest WHERE membershipID = '" + member.getMembershipID() + "'");
            conn.commit();
            
            //build a list of Areas of Interest
            while(rs.next()){
                member.addInterest(AreaOfInterestDAO.getInstance().read(rs.getString("InterestID"),conn));
            }
//            stmt.close();
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return member;
    }
    
    /**
     * Read in a list of all members.
     * 
     * @return an array list of the members in the database
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<MembershipBO> readAll() throws SQLException, DataException, ConnectionPoolException {
        // create list to store members
        ArrayList<MembershipBO> mList = new ArrayList<MembershipBO>();
        
        Connection conn = ConnectionPool.getInstance().get();
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Membership");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            MembershipBO member = new MembershipBO(rs.getString("membershipid"));
            member.setCreditCardType(rs.getString("creditcardtype"));
            member.setCreditCardNum(rs.getString("creditcardnum"));
            member.setCreditCardExpMonth(rs.getInt("creditcardexpmonth"));
            member.setCreditCardExpYear(rs.getInt("creditcardexpyear"));
            member.setMemberStartDate(rs.getDate("memberstartdate").toString());
            member.setMemberExpireDate(rs.getDate("memberexpiredate").toString());
            if(rs.getString("BackupExpDate") != null){
                member.setBuExpDate(rs.getDate("BackupExpDate").toString());
                member.setBuSize(rs.getDouble("BackupSize"));
            }else{
                String is = null;
                member.setBuExpDate(null);
                member.setBuSize(0.0);
            }
            member.setMembPwd(rs.getString("membPwd"));
            member.setAlreadyInDB(true);
            member.setIsDirty(false);
            
            //construct sub-objects: Area of Interest List
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * FROM Member_Interest WHERE membershipID = '" + member.getMembershipID() + "'");
            conn.commit();
            
            //build a list of Areas of Interest
            while(rs.next()){
                member.addInterest(AreaOfInterestDAO.getInstance().read(rs.getString("InterestID"),conn));
            }
            stmt.close();
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        // return the BO
        return mList;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method for Membership.
     * 
     * @param memberIn contains the MembershipBO to be saved
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized void save(MembershipBO memberIn) throws DataException, ConnectionPoolException {
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(memberIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + memberIn.getMembershipID(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        return;
    }
    
    /**
     * This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the MembershipDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.
     * 
     * 
     * 
     * @param member contains the MembershipBO to be saved
     * @param conn contains the previously established connection
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    public synchronized void save(MembershipBO member, Connection conn) throws SQLException, DataException {
        // run update or insert
        if (member.isIsDirty()) {
            if (member.isAlreadyInDB()) {
                update(member, conn);
            }else{
                insert(member, conn);
            }
            // set the dirty flag to false now that we've saved it
            member.setIsDirty(false);
        }
        
        // call save(bo, conn) on any subobjects (like CustomerDAO to MembershipDAO)
        if(member.getCust() != null){   //if the membership object is accessed without accessing it's customer (rare occasion)
            if(member.getCust().isIsDirty() == true){
                try {
                    CustomerDAO.getInstance().save(member.getCust(), conn);
                } catch (DataException ex) {
                    ex.printStackTrace();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                } catch (ConnectionPoolException ex) {
                    ex.printStackTrace();
                }
            }
        }
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(member.getMembershipID(), member);
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * @param member contains the MembershipBO to be saved
     * @param conn contains the previously established connection
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    private synchronized void update(MembershipBO member, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Membership SET creditCardType = ?, creditCardNum = ?, " +
                "creditCardExpMonth = ?, creditCardExpYear = ?, memberStartDate = ?, memberExpireDate = ?, " +
                "BackupExpDate = ?, BackupSize = ?, MembPwd = ? WHERE MembershipID = ?");
        pstmt.setString(1, member.getCreditCardType());
        pstmt.setString(2, member.getCreditCardNum());
        pstmt.setInt(3, member.getCreditCardExpMonth());
        pstmt.setInt(4, member.getCreditCardExpYear());
        pstmt.setDate(5, java.sql.Date.valueOf(member.getMemberStartDate()));
        pstmt.setDate(6, java.sql.Date.valueOf(member.getMemberExpireDate()));
        if(member.getBuExpDate() != null){
            pstmt.setDate(7, java.sql.Date.valueOf(member.getBuExpDate()));
            pstmt.setDouble(8, member.getBuSize());
        }else{
            pstmt.setNull(7, java.sql.Types.DATE);
            pstmt.setNull(8, java.sql.Types.DOUBLE);
        }
        pstmt.setString(9, member.getMembPwd());
        pstmt.setString(10, member.getMembershipID());
        pstmt.executeUpdate();
        
        //update the Member_Interest table
        if(member.getInterestList() != null){
            for(int i=0; i < member.getInterestList().size(); i++){
                //check the DB for the member_interest set
                pstmt = conn.prepareStatement("SELECT * FROM Member_Interest WHERE MembershipID = ? AND InterestID = ?");
                pstmt.setString(1, member.getMembershipID());
                pstmt.setString(2, member.getInterestList().get(i).getAOI_ID());
                ResultSet rs = pstmt.executeQuery();
                conn.commit();
                
                //if the set does not exist in the DB, add it
                if(rs.next() == false){
                    pstmt = conn.prepareStatement("INSERT INTO Member_Interest (MembershipID, InterestID) VALUES (?, ?)");
                    pstmt.setString(1, member.getMembershipID());
                    pstmt.setString(2, member.getInterestList().get(i).getAOI_ID());
                    pstmt.executeUpdate();
                    conn.commit();
                }
            }
        }
        pstmt.close();
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * 
     * @param member contains the MembershipBO to be saved
     * @param conn contains the previously established connection
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    private synchronized void insert(MembershipBO member, Connection conn) throws SQLException, DataException {
        //convert Date to SQL Date
        
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Membership (MembershipID, creditCardType, creditCardNum, creditCardExpMonth, " +
                "creditCardExpYear, MemberStartDate, MemberExpireDate, BackupExpDate, BackupSize, MembPwd) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, member.getMembershipID());
        pstmt.setString(2, member.getCreditCardType());
        pstmt.setString(3, member.getCreditCardNum());
        pstmt.setInt(4, member.getCreditCardExpMonth());
        pstmt.setInt(5, member.getCreditCardExpYear());
        pstmt.setDate(6, java.sql.Date.valueOf(member.getMemberStartDate()));
        pstmt.setDate(7, java.sql.Date.valueOf(member.getMemberExpireDate()));
        if(member.getBuExpDate() != null){
            pstmt.setDate(8, java.sql.Date.valueOf(member.getBuExpDate()));
            pstmt.setDouble(9, member.getBuSize());
        }else{
            pstmt.setNull(8, java.sql.Types.DATE);
            pstmt.setNull(9, java.sql.Types.DOUBLE);
        }
        pstmt.setString(10, member.getMembPwd());
        pstmt.executeUpdate();
        
        pstmt = conn.prepareStatement("INSERT INTO Member_Interest (MembershipID, InterestID) VALUES (?, ?)");
        //update the Member_Interest table
        if(member.getInterestList() != null){
            for(int i=0; i < member.getInterestList().size(); i++){
                pstmt.setString(1, member.getMembershipID());
                pstmt.setString(2, member.getInterestList().get(i).getAOI_ID());
                pstmt.executeUpdate();
            }
        }
        // tell the object that it's now in the db (so we call update next time not insert)
        member.setAlreadyInDB(true);
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    /** I did not include the DELETE functionality intentionally due to instructions received, as well as
     * to protect the integrity of the data.
     */
    
    /**
     * The DELETE cabability is necessary for the MEMBER_INTEREST table due to the inherent flexibility of
     * the Areas of Interest for a member.
     * 
     * This method removes Areas Of Interest from the indicated member's list in the DB.
     * 
     * 
     * @param member contains the member to which the Areas of interest pertain
     * @param index contains the index of the Area of interest to be removed from the list contained in the MemberBO.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public void removeInterest(MembershipBO member, int index) throws ConnectionPoolException, DataException, SQLException{
        
        //remove from DB
        Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
        PreparedStatement pstmt = conn.prepareStatement("Select * FROM Member_Interest WHERE MembershipID = ? AND InterestID = ?");
        pstmt.setString(1, member.getMembershipID());
        pstmt.setString(2, member.getInterestList().get(index).getAOI_ID());
        ResultSet rs = pstmt.executeQuery();
        
        //if the item is in the DB, remove it
        while(rs.next()){
            pstmt = conn.prepareStatement("DELETE FROM Member_Interest WHERE MembershipID = ? AND InterestID = ?");
            pstmt.setString(1, member.getMembershipID());
            pstmt.setString(2, member.getInterestList().get(index).getAOI_ID());
            pstmt.executeUpdate();
        }
        
        //remove from the ArrayList
        member.removeInterest(index);
        
        pstmt.close();
        conn.commit();
        ConnectionPool.getInstance().release(conn);
    }
}
